﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;
using System.Text;

namespace FreeOnlineApplication.Class.Repository
{
    public class FacilityTransactionRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();

        public FacilityTransactionRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(FacilityTransaction facilitytr)
        {
            string insertSQL = String.Format(@"insert into table_facilitytransaction 
                (
                    facilitytr_code,
                    checkin_id,
                    facilitytr_totalamount,
                    unit_id) 
                VALUES ('{0}','{1}',{2},{3})",
                facilitytr.CODE, facilitytr.CHECK_IN.ID, facilitytr.TOTAL_AMOUNT, facilitytr.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            facilitytr.ID = GetMaximumId();

            foreach (FacilityTransactionItem itm in facilitytr.FACILITY_ITEMS)
            {
                insertSQL = String.Format(@"insert into table_facilitytransactionitem
                (
                    facilitytr_id,
                    facility_id,
                    facilitytritem_qty,
                    facilitytritem_price,
                    facilitytritem_subtotal) 
                VALUES ('{0}','{1}',{2},{3},{4})",
                               facilitytr.ID, itm.FACILITY.ID, itm.QUANTITY, itm.PRICE, itm.SUB_AMOUNT);
                aCommand.CommandText = insertSQL;
                aCommand.ExecuteNonQuery();
                itm.ID = GetMaximumItemId();
            }
        }
        public FacilityTransaction GetById(int id)
        {
            string getByID = String.Format("select * from table_facilitytransaction where facilitytr_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            FacilityTransaction facilitytr = transform(aReader);
            aReader.Close();

            string getItemByTrID = String.Format("select * from table_facilitytransactionitem where facilitytr_id = {0}", id);
            aCommand.CommandText = getItemByTrID;
            aReader = aCommand.ExecuteReader();
            facilitytr.FACILITY_ITEMS = transformItemList(facilitytr, aReader);
            aReader.Close();

            return facilitytr;
        }
        public void Update(FacilityTransaction facilitytr)
        {
            string updateSQL = String.Format(@"update table_facilitytransaction set 
                     facilitytr_code= '{0}',
                    checkin_id= '{1}',
                    facilitytr_totalamount = {2},
                    unit_id= {3}
                where facilitytr_id = {4}",
                facilitytr.CODE, facilitytr.CHECK_IN.ID, facilitytr.TOTAL_AMOUNT, facilitytr.UNIT.ID, facilitytr.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();


            string getItemByTrID = String.Format("select * from table_facilitytransactionitem where facilitytr_id = {0}", facilitytr.ID);
            aCommand.CommandText = getItemByTrID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList t = transformItemList(facilitytr, aReader);
            aReader.Close();
            StringBuilder sb = new StringBuilder();
            foreach (FacilityTransactionItem itm in facilitytr.FACILITY_ITEMS)
            {
                if (t.Contains(itm))
                {
                    updateSQL = String.Format(@"update table_facilitytransactionitem set
                    facility_id={0},
                    facilitytritem_qty={1},
                    facilitytritem_price={2},
                    facilitytritem_subtotal ={3}
                    where facilitytritm_id = {4}",
                                itm.FACILITY.ID, itm.QUANTITY, itm.PRICE, itm.SUB_AMOUNT, itm.ID);
                    aCommand.CommandText = updateSQL;
                    aCommand.ExecuteNonQuery();
                }
                else
                {
                    updateSQL = String.Format(@"insert into table_facilitytransactionitem
                        (
                    facilitytr_id,
                    facility_id,
                    facilitytritem_qty,
                    facilitytritem_price,
                    facilitytritem_subtotal) 
                            VALUES ('{0}','{1}',{2},{3},{4})",
                                                  facilitytr.ID, itm.FACILITY.ID, itm.QUANTITY,itm.PRICE, itm.SUB_AMOUNT);
                    aCommand.CommandText = updateSQL;
                    aCommand.ExecuteNonQuery();
                    itm.ID = GetMaximumItemId();
                }
                sb.Append(itm.ID);
                sb.Append(",");
            }
            string notIn = sb.ToString();
            if (notIn == ",") return;
            notIn = notIn.Length < 1 ? "" : notIn.Substring(0,notIn.Length - 1);
            string deleteNotIn = "delete from table_facilitytransactionitem where facilitytritm_id not in (" + notIn + ")";
            aCommand.CommandText = deleteNotIn;
            aCommand.ExecuteNonQuery();

        }
        public void Delete(FacilityTransaction facilitytr)
        {
            string deleteSQL = "delete from table_facilitytransactionitem where facilitytr_id = " + facilitytr.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();

            deleteSQL = "delete from table_facilitytransaction where facilitytr_id = " + facilitytr.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(facilitytr_id) from table_facilitytransaction");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetMaximumItemId()
        {
            string getByID = String.Format("SELECT max(facilitytritm_id) from table_facilitytransactionitem");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_facilitytransaction where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int facilitytr)
        {
            string sql = String.Format("select * from table_facilitytransaction where unit_id = {0}", facilitytr);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }

        public IList GetAllSearch(string find, int unit)
        {
            string sql = String.Format(@"select tr.* from table_facilitytransaction tr, table_checkin t, table_customer c where 
            concat(
                tr.facilitytr_code,tr.facilitytr_totalamount,
                c.customer_code, c.customer_idno, c.customer_name,
                c.customer_address, c.customer_email, c.customer_phone, c.customer_job,
                c.customer_gender, t.checkin_code, 
                DATE_FORMAT(t.checkin_date, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.checkin_start, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.checkin_end, '%d-%m-%Y %H:%i:%s'),t.checkin_status
                ) like '%{0}%' and t.unit_id = {1}
                and t.customer_id = c.customer_id
                and tr.checkin_id = t.checkin_id", find, unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private FacilityTransaction transform(MySqlDataReader aReader)
        {
            FacilityTransaction facilitytr = null;
            while (aReader.Read())
            {
                facilitytr = new FacilityTransaction();
                facilitytr.ID = Convert.ToInt32(aReader["facilitytr_id"]);
                facilitytr.CODE = aReader["facilitytr_code"].ToString();
                facilitytr.CHECK_IN = new CheckIn(Convert.ToInt32(aReader["checkin_id"]));
                facilitytr.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                facilitytr.TOTAL_AMOUNT = Convert.ToDouble(aReader["facilitytr_totalamount"]);
            }
            return facilitytr;
        }
        private FacilityTransactionItem transformItem(MySqlDataReader aReader)
        {
            FacilityTransactionItem facilitytr = null;
            while (aReader.Read())
            {
                facilitytr = new FacilityTransactionItem();
                facilitytr.ID = Convert.ToInt32(aReader["facilitytritm_id"]);
                facilitytr.FACILITY = new Facility(Convert.ToInt32(aReader["facility_id"]));
                facilitytr.FACILITY_TRANSACTION = new FacilityTransaction(Convert.ToInt32(aReader["facilitytr_id"]));
                facilitytr.QUANTITY = Convert.ToDouble(aReader["facilitytritem_qty"]);
                facilitytr.PRICE = Convert.ToDouble(aReader["facilitytritem_price"]);
                facilitytr.SUB_AMOUNT = Convert.ToDouble(aReader["facilitytritem_subtotal"]);
            }
            return facilitytr;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                FacilityTransaction facilitytr = new FacilityTransaction();
                facilitytr.ID = Convert.ToInt32(aReader["facilitytr_id"]);
                facilitytr.CODE = aReader["facilitytr_code"].ToString();
                facilitytr.CHECK_IN = new CheckIn(Convert.ToInt32(aReader["checkin_id"]));
                facilitytr.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                facilitytr.TOTAL_AMOUNT = Convert.ToDouble(aReader["facilitytr_totalamount"]);
                result.Add(facilitytr);
            }
            return result;
        }
        private IList transformItemList(FacilityTransaction transaction, MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                FacilityTransactionItem facilitytr = new FacilityTransactionItem();
                facilitytr.ID = Convert.ToInt32(aReader["facilitytritm_id"]);
                facilitytr.FACILITY = new Facility(Convert.ToInt32(aReader["facility_id"]));
                facilitytr.FACILITY_TRANSACTION = transaction == null ? new FacilityTransaction(Convert.ToInt32(aReader["facilitytr_id"])) : transaction;
                facilitytr.PRICE = Convert.ToDouble(aReader["facilitytritem_price"]);
                facilitytr.QUANTITY = Convert.ToDouble(aReader["facilitytritem_qty"]);
                facilitytr.SUB_AMOUNT = Convert.ToDouble(aReader["facilitytritem_subtotal"]);
                result.Add(facilitytr);
            }
            return result;
        }
    }
}
